/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.fiap.dao;

import br.com.fiap.vo.UsuarioVO;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Fabianoss
 */
public class UsuarioDAO {
    
    private UsuarioVO usuarioVO;
    private String query;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet result;
    private FabricaConexao fb;
    
    public UsuarioDAO(){
        fb = FabricaConexao.getInstance();
    }    
    
    public void Salvar(UsuarioVO usuarioVO){
        this.query = "INSERT INTO `avaliacao1`.`usuario` (`role`,`login`,`senha`,`idpessoa`)VALUES(?,?,?,?);";
          try {   
            this.pstmt = fb.getConnection().prepareStatement(query);
            this.pstmt.setString(1, usuarioVO.getRole());
            this.pstmt.setString(2, usuarioVO.getUsuario());
            this.pstmt.setString(3,usuarioVO.getSenha());
            this.pstmt.setInt(4,usuarioVO.getIdPessoa());            
            this.pstmt.execute(); 
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao inserir Usuario.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, null);
        }
    }
    
    public void update(UsuarioVO usuarioVO){
        this.query = "UPDATE `avaliacao1`.`usuario`SET `role` = ?,`login` = ?,`senha` = ?,`idpessoa` = ? WHERE idusuario = ?;";
          try {
            this.pstmt = fb.getConnection().prepareStatement(query);
            this.pstmt.setString(1, usuarioVO.getRole());
            this.pstmt.setString(2, usuarioVO.getUsuario());
            this.pstmt.setString(3,usuarioVO.getSenha());
            this.pstmt.setInt(4,usuarioVO.getIdPessoa());
            this.pstmt.setInt(5, usuarioVO.getidUsuario());
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao inserir Usuario.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, null);
        }
    }
    
    public void delete(UsuarioVO usuarioVO){        
       this.query = "DELETE FROM `avaliacao1`.`usuario`WHERE idusuario = ?;";
          try {
            this.pstmt = fb.getConnection().prepareStatement(query);
            this.pstmt.setInt(1, usuarioVO.getidUsuario());
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao deletar Usuario.: "+ e.getMessage());
        }finally{
              fb.closeConnection(pstmt, null, null);
        }
        
    }
    
    public List<UsuarioVO> listarUsuariosVO(){
        List<UsuarioVO> lista = new ArrayList<UsuarioVO>();
        this.query = "SELECT * FROM `avaliacao1`.`usuario`;";
          try {
             this.stmt = fb.getConnection().createStatement();
             this.result = stmt.executeQuery(query);
             
             while(result.next()){
                 UsuarioVO vo = new UsuarioVO();
                 vo.setidUsuario(result.getInt("idusuario"));
                 vo.setRole(result.getString("role"));
                 vo.setUsuario(result.getString("login"));
                 vo.setSenha(result.getString("senha"));
                 vo.setIdPessoa(result.getInt("idpessoa"));
                 lista.add(vo);
             }            
            
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao deletar Usuario.: "+ e.getMessage());
        }finally{
              fb.closeConnection(null, stmt, result);
        }
        return lista;  
    }
    
     public UsuarioVO getUsuarioVOById(int id){
        
        this.query = "SELECT * FROM `avaliacao1`.`usuario` where idAluno = ?;";
        UsuarioVO vo = new UsuarioVO(); 
        try {
             this.stmt = fb.getConnection().createStatement();
             this.result = stmt.executeQuery(query);
             
             while(result.next()){
                
                 vo.setidUsuario(result.getInt("idusuario"));
                 vo.setRole(result.getString("role"));
                 vo.setUsuario(result.getString("login"));
                 vo.setSenha(result.getString("senha"));
                 vo.setIdPessoa(result.getInt("idpessoa"));
                 
             }            
            
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao selecionar Usuario.: "+ e.getMessage());
        }finally{
               fb.closeConnection(null, stmt, result);
        }
        return vo;  
    }
    
     public UsuarioVO getUsuarioVOByLogin(String login){
        
        this.query = "SELECT * FROM `avaliacao1`.`usuario` where login = '"+login+"';";
        UsuarioVO vo = null; 
        try {
             this.stmt = fb.getConnection().createStatement();
             this.result = stmt.executeQuery(query);
             
             while(result.next()){
                 vo = new UsuarioVO();
                 vo.setidUsuario(result.getInt("idusuario"));
                 vo.setRole(result.getString("role"));
                 vo.setUsuario(result.getString("login"));
                 vo.setSenha(result.getString("senha"));
                 vo.setIdPessoa(result.getInt("idpessoa"));
                 
             }            
            
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Erro ao selecionar Usuario.: "+ e.getMessage());
        }finally{
             fb.closeConnection(null, stmt, result);
        }
        return vo;  
    }
     
    
}

    